package main;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.util.Locale;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableModel;

public class connect {
	private static String fName = "Game.config";
	 public static String Dev_URL() {
		 String sitio = "http://www.winegame.org/admin/";
		 return sitio;
	 }
	 public static Connection connect_DB() {
         Connection conn = null;
       try {
    	   String url = "jdbc:mysql://winegame.ctqo8fsbjphq.us-east-1.rds.amazonaws.com:3306/";
    	  //String url = "jdbc:mysql://127.0.0.1:3306/";
           
            String dbName = "winegame";
            String userName =  "root";
            String password =  "agronomo";
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conn = DriverManager.getConnection(url + dbName, userName, password);
            } catch (ClassNotFoundException e) {;
                System.exit(1);
            } catch (SQLException e) {
                JOptionPane.showMessageDialog( null,
             "Error in DB connection!",
             "Error", JOptionPane.ERROR_MESSAGE);
            }

        } catch (Exception ex) {
            Logger.getLogger(main_window.class.getName()).log(Level.SEVERE, null, ex);
        }
         return conn;
  }
	 public static String readProp(String Clave) throws java.lang.Exception {
	        try {
	            java.util.Properties vProp = new java.util.Properties();
	            java.io.FileInputStream iFile = new java.io.FileInputStream(fName);

	            vProp.load(iFile);
	            String tmpStr = new String();
	            tmpStr = vProp.getProperty(Clave);
	            return tmpStr;

	        } catch (java.lang.Exception ex) {
	            throw new java.lang.Exception("[readProp01]" + ex.getMessage());
	        }

	    }
	 public static void writeProp(String clave, String valor) throws java.lang.Exception {
	        try {
	            java.util.Properties vProp = new java.util.Properties();

	             try{

	            FileInputStream in = new FileInputStream(fName);
	            vProp.load(in);
	             }
	             catch(IOException e){
	                }

	            vProp.setProperty(clave, valor);
	            java.io.FileOutputStream configFile = new java.io.FileOutputStream(fName);
	            vProp.store(configFile, "Configuration File");
	            configFile.close();

	        } catch (java.lang.Exception ex) {
	            throw new java.lang.Exception("[clsTest01]" + ex.getMessage());
	        }
	    }
	
	 public static int[] getStatus() {
		 int[] stat = new int[6];
		 try {
			  String query = "SELECT Winery, Importer, Distributor, Retailer, week FROM game_status WHERE (Game_ID=?)";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
				 stat[0]=rs.getInt(1);
				 stat[1]=rs.getInt(2);
				 stat[2]=rs.getInt(3);
				 stat[3]=rs.getInt(4);
				 stat[4]=rs.getInt(1)+rs.getInt(2)+rs.getInt(3)+rs.getInt(4);
				 stat[5]=rs.getInt(5);
			  }
			  rs.close();
			  ps.close();
			  conn.close();
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return stat;
	 }
	 public static void upStatcon() {
		 try {
			  String query = "UPDATE games SET rep_imp=NOW() WHERE ID=?";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query);
			  ps.setInt(1, values.getIDGame());
			  ps.execute();
			  ps.close();
			  conn.close();
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 
	 }
	 public static void pop_tablagames(JTable Tabla) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[5];
		    try {
		  		  String query = "SELECT ID, Game_Name, Game_Description, pass, sem FROM view_game_stat Order by ID ASC";
		  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  result[0]=rs.getInt(1); 
		  			 result[1]=rs.getString(2); 
		  			 result[2]=rs.getString(3); 
		  			 if (rs.getInt(4)==1) {
		  				result[3]="Yes"; 
		  			 } else {
		  				result[3]="No";
		  			 }
		  			if (rs.getInt(5)==0) {
		  				result[4]="Not Started"; 
		  			 } else {
		  				result[4]=rs.getString(5);
		  			 }
		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
				    rs.close();
				    ps.close();
				    conn.close();
				    Tabla.getColumnModel().getColumn(0).setMaxWidth(0);
				    Tabla.getColumnModel().getColumn(0).setMinWidth(0);
				    Tabla.getColumnModel().getColumn(0).setWidth(0);
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 }
	 public static boolean lookPass(String pas, int ID){
		 boolean check = false;
		 try {
			  String query = "SELECT passwd FROM games WHERE ((ID=?))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,ID);
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
				if (rs.getString(1).compareTo(pas)==0) check=true;
			  }
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 
		 return check;
	 }
	 public static void pop_tablaOrd(JTable Tabla, int lev) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[7];
		    try {
		  		  String query = "SELECT Semana, Order_Red,Red_back,Red_Ship,Order_white,White_Back,White_Ship FROM orders_inv_back_t WHERE ((Game_ID=?) AND (Level=?)) Order by Semana DESC";
		  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, lev);
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  for (int i=0;i<7;i++) {
		  				  
		  				result[i] = rs.getInt(i+1);
		  			  }
		  			  String query2 = "SELECT Order_Red, Order_white FROM orders WHERE ((Game_ID=?) AND (Level=?) AND (Semana=?)) Order by Semana DESC";
		  			  PreparedStatement  ps2 = conn.prepareStatement(query2);
			  		  ps2.setInt(1, values.getIDGame());
			  		  ps2.setInt(2, lev+1);
			  		 ps2.setInt(3, rs.getInt(1));
			  		 ResultSet rs2 = ps2.executeQuery();
			  		  while ( rs2.next() ) {
			  			result[1] = rs2.getInt(1);
			  			result[4] = rs2.getInt(2);  	  				
			  			  }
			  		  rs2.close();
			  		  ps2.close();
		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
		  		  
				    rs.close();
				    ps.close();
				    conn.close();
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 } 
	 
	 public static void pop_tablaOrd2(JTable Tabla, int lev) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[5];
		    try {
		  		  String query = "SELECT Semana, Order_Red,Red_back,Order_white,White_Back FROM orders_inv_back_t WHERE ((Game_ID=?) AND (Level=?) AND (Semana<=?)) Order by Semana DESC";
		  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, lev);
		  		  ps.setInt(3, values.getWeek());
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  for (int i=0;i<5;i++) {
		  				  
		  				result[i] = rs.getInt(i+1);
		  			  }
	
		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
		  		  
				    rs.close();
				    ps.close();
				    conn.close();
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 } 
	 
	 public static void pop_tablaInvback(JTable Tabla, int lev) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[5];
		    try {
		  		  String query = "SELECT Semana, Red_Inv,Red_back,White_Inv,White_Back FROM orders_inv_back WHERE ((Game_ID=?) AND (Level=?) AND (Semana<=?)) Order by Semana DESC";
		  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, lev);
		  		  ps.setInt(3, values.getWeek());
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  for (int i=0;i<5;i++){
		  				result[i] = rs.getInt(i+1);
		  			  }
	
		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
				    rs.close();
				    ps.close();
				    conn.close();
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 } 
	 public static void pop_tablaShip(JTable Tabla, int lev) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[5];
		    try {
		  		  String query = "SELECT Semana, Units, Ship_Red, Ship_White FROM shipping WHERE ((Game_ID=?) AND (Level=?)) Order by Semana DESC";
		  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, lev);
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  for (int i=0;i<4;i++){
		  				result[i] = rs.getInt(i+1);
		  			  }
		  			result[4] = rs.getInt(1)+2;
		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
				    rs.close();
				    ps.close();
				    conn.close();
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 } 
	 
	 public static void pop_tablaBot(JTable Tabla, int lev) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[4];
		    try {
		  		  String query = "SELECT Semana, Bot_Red, Bot_White FROM production WHERE ((Game_ID=?)) Order by Semana DESC";
		  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  for (int i=0;i<3;i++){
		  				result[i] = rs.getInt(i+1);
		  			  }
		  			result[3] = rs.getInt(1)+1;
		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
				    rs.close();
				    ps.close();
				    conn.close();
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 } 
	 
	 public static void pop_tablaOrden(JTable Tabla, int lev) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[6];
		    try {
		  		  String query = "SELECT Semana, Order_Red, Order_White FROM orders WHERE ((Game_ID=?) AND (Level=1) and (Semana<=?)) Order by Semana DESC";	  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, values.getWeek());
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  result[0] = rs.getInt(1);
		  			  result[1] = rs.getInt(1)+5;
		  			  result[2] = rs.getInt(3);
		  			result[3] = "";
		  			result[4] = rs.getInt(2);
		  			result[5] = "";
 		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
		  		  rs.close();
		  		  ps.close();
		  		  for (int i=0;i<Tabla.getModel().getRowCount();i++) {
		  			  int val = Integer.parseInt(Tabla.getModel().getValueAt(i,0).toString());
		  			  if (val<=(values.getWeek())) {
		  				 query = "SELECT Red_Ship, White_Ship FROM orders_inv_back WHERE ((Game_ID=?) AND (Level=0) AND (ava_sem=?))";	  		
				  		  ps = conn.prepareStatement(query);
				  		  ps.setInt(1, values.getIDGame());
				  		  ps.setInt(2, val);
				  		   rs = ps.executeQuery();
				  		  while ( rs.next() ) {
				  			Tabla.getModel().setValueAt(rs.getInt(2), i ,3);
				  			Tabla.getModel().setValueAt(rs.getInt(1), i ,5);	 
				  			 }

		  			  }
		  		  }
		  		  
				    rs.close();
				    ps.close();
				    conn.close();
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 } 
	 
	 public static void pop_tablaDemand(JTable Tabla, int lev) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[3];
		    try {
		  		  String query = "SELECT Semana, Order_Red, Order_White FROM orders WHERE ((Game_ID=?) AND (Level=?)) Order by Semana DESC";	  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, lev);
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  result[0] = rs.getInt(1);
		  			  result[1] = rs.getInt(3);
		  			  result[2] = rs.getInt(2);
		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
		  		 rs.close();
				    ps.close();
				    conn.close();
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 } 
	 
	 public static int[] getOrders(int level) {
		 int[] stat = new int[4];
		 try {
			  String query = "SELECT Red_Back, White_back FROM orders_inv_back WHERE ((Game_ID=?) AND (Semana=?) AND (Level=?))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2, values.getWeek());
			  ps.setInt(3, level-1);
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
				 stat[1]=rs.getInt(1);
				 stat[3]=rs.getInt(2);
			  }
					  query = "SELECT Order_Red,Order_White FROM orders WHERE ((Game_ID=?) AND (Semana=?) AND (Level=?))";
					  ps = conn.prepareStatement(query); 
					  ps.setInt(1,values.getIDGame());
					  ps.setInt(2, values.getWeek());
					  ps.setInt(3, level);
					  rs = ps.executeQuery();
					  while ( rs.next() ) {
						 stat[0]=rs.getInt(1);
						 stat[2]=rs.getInt(2);
					  }
			  
			  
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return stat;
	 }
	 public static int[] getProd() {
		 int[] stat = new int[2];
		 try {
			  String query = "SELECT Bot_red, Bot_White FROM production WHERE ((Game_ID=?) AND (Semana=?))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2, values.getWeek()-1);
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
				 stat[0]=rs.getInt(1);
				 stat[1]=rs.getInt(2);
			  }
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return stat;
	 }
	 
	 public static void pop_tablaLin(JTable Tabla, int Line) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[5];
		    try {
		  		  String query = "SELECT ID, tipo, quant, prod, setup FROM bottling WHERE ((ID_Game=?) AND (Sem=?) AND (Line=?)) Order by Seq ASC";
		  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, values.getWeek());
		  		  ps.setInt(3, Line);
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  for (int i=0;i<5;i++){
		  				  if (i==1) {
		  					if (rs.getInt(2)==1) {
		  						result[1]= "White";
		  					}else {
		  						result[1]= "Red";
		  					}
		  				  } else {
		  				result[i] = rs.getInt(i+1);
		  				  }
		  			  }
		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
				    rs.close();
				    ps.close();
				    conn.close();
				    Tabla.getColumnModel().getColumn(0).setMaxWidth(0);
				    Tabla.getColumnModel().getColumn(0).setMinWidth(0);
				    Tabla.getColumnModel().getColumn(0).setWidth(0);
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 }
	 
	 public static String getProd(int tipo, int Line) {
		 String stat="";
		 try {
			 String adic="";
			 if (tipo<3) {
				 adic= " AND (tipo=?)";
			 }
			 int tot=0;
			 String query = "SELECT SUM(quant), SUM(Setup) FROM bottling WHERE ((ID_Game=?) AND (Sem=?) AND (Line=?)"+adic+")";

			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 

			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2, values.getWeek());
			  ps.setInt(3, Line);
				 if (tipo<3) {
			 ps.setInt(4, tipo);
				 }
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
				  if (tipo!=3) {
				 tot=rs.getInt(1);
				  } else if (tipo==3){
			     tot=rs.getInt(1)+rs.getInt(2);  
				  } else if (tipo==4) {
					  tot=rs.getInt(1);  
				  }
			  }
			  stat = Integer.toString(tot);
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return stat;
	 }
	 
	 public static void check_setup(int Line) throws SQLException {
		 int[][] setup = new int [2][2];
		 
		    try {
		    	String query = "SELECT de, a, tiempo FROM setup_times";
		    	 Connection conn =  connect_DB();
		  		 PreparedStatement ps = conn.prepareStatement(query);
		  		 ResultSet rs = ps.executeQuery();
		  		while ( rs.next() ) {
		  		setup[rs.getInt(1)-1][rs.getInt(2)-1] = rs.getInt(3);
		  		}
		    	rs.close();
		    	ps.close();
		    	int Sequ=0;
		  	    query = "SELECT Sem, tipo, ID FROM bottling WHERE ((ID_Game=?) AND (Sem>=?) AND (Line=?)) Order by Sem, Seq ASC";
		  		 int prev_tip =-1;
		  		  conn =  connect_DB();
		  		  ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, values.getWeek()-1);
		  		  ps.setInt(3, Line);
		  		  rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  if (rs.getInt(1)==values.getWeek()-1) {
		  				  prev_tip = rs.getInt(2);
		  			  } else {
		  				  if (prev_tip==-1) {
				  				String  query2 = "UPDATE bottling SET setup=?, Seq=? WHERE (ID=?)";
				  				PreparedStatement ps2 = conn.prepareStatement(query2);
				  				ps2.setInt(1, 0);
				  				ps2.setInt(2,Sequ);
				  				ps2.setInt(3, rs.getInt(3));
				  				ps2.execute();
				  				prev_tip = rs.getInt(2);
				  				Sequ++; 
		  				  } else {
		  				String  query2 = "UPDATE bottling SET setup=?, Seq=? WHERE (ID=?)";
		  				PreparedStatement ps2 = conn.prepareStatement(query2);
		  				ps2.setInt(1, setup[prev_tip-1][rs.getInt(2)-1]);
		  				ps2.setInt(2,Sequ);
		  				ps2.setInt(3, rs.getInt(3));
		  				ps2.execute();
		  				prev_tip = rs.getInt(2);
		  				Sequ++;
		  				  }
		  			  }
		  		  }
				    rs.close();
				    ps.close();
				    conn.close();
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 }
	 
	 public static String getPrevWine(int Line) {
		 String stat="";
		 try {

			 int tot=-1;
			 String query = "SELECT tipo FROM bottling WHERE ((ID_Game=?) AND (Sem=?) AND (Line=?) AND (SEQ=(SELECT Max(Seq) From Bottling WHERE (ID_Game=?) AND (Sem=?) AND (Line=?))))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2, values.getWeek()-1);
			  ps.setInt(3, Line);
			  ps.setInt(4,values.getIDGame());
			  ps.setInt(5, values.getWeek()-1);
			  ps.setInt(6, Line);
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
					  tot=rs.getInt(1);  
				  }
			
			  if (tot==1) {
				  stat = "White"; 
			  } else if (tot==2) {
				  stat = "Red"; 
			  } else {
				  stat = "none";
			  }

		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return stat;
	 }
	 
	 public static int[] getWineAvailW(int Tipo) {
		 int[] ava = new int[3];
		 try {

			 int tot=-1;
			 String Vino="";
			 String ship="";
			 if (Tipo==1) {
				 Vino="White_Inv";
				 ship="White_ship";
			 } else {
				 Vino="Red_Inv"; 
				 ship="Red_ship";
			 }
			 
			 String query = "SELECT "+Vino+" FROM orders_inv_back WHERE ((Game_ID=?) AND (Semana=?) AND (Level=1))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2, values.getWeek());
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
					  ava[0]=rs.getInt(1);  
			}
			  rs.close();
			  ps.close();
			  query = "SELECT "+ship+" FROM orders_inv_back WHERE ((Game_ID=?) AND (ava_sem=?) AND (Level=0))";
			  ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2, values.getWeek());
			  rs = ps.executeQuery();
			  while ( rs.next() ) {
					  ava[1]=rs.getInt(1);  
			}
			  rs.close();
			  ps.close();
			  conn.close();
		  ava[2]=ava[0]+ava[1];

		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return ava;
	 }
	 
	 public static boolean ckWineAvail(JTable Tabla, int max, int col, int cur, int tru) {
		 boolean res;
		 int filas = ((DefaultTableModel)Tabla.getModel()).getRowCount();
		 int swhite =(cur*tru);
		 for (int i=0;i<filas;i++) {
			 swhite= swhite + ((Integer.parseInt(Tabla.getModel().getValueAt(i, col).toString()))*(Integer.parseInt(Tabla.getModel().getValueAt(i, 1).toString())));
		 }
		 System.out.println("q "+swhite + " "+tru + " col " + col);
		 if (swhite>max) {
			 res=false;  
		 } else {
			 res=true;
		 }
		 return res;
	 }
	 
	 public static int getOrderT(int Tipo) {
		 int stat=0;
		 try {


			 String Vino="";
			 String Back="";
			 if (Tipo==1) {
				 Vino="Order_white";
				 Back="White_back";
			 } else {
				 Vino="Order_red";
				 Back="Red_Back";
			 }
			 
			 String query = "SELECT "+Vino+" FROM orders WHERE ((Game_ID=?) AND (Semana=?) AND (Level=1))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2, values.getWeek());
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
					  stat=rs.getInt(1);  
			}
			   query = "SELECT "+Back+" FROM orders_inv_back WHERE ((Game_ID=?) AND (Semana=?) AND (Level=1))";
			   ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2, values.getWeek());
			  rs = ps.executeQuery();
			  while ( rs.next() ) {
				  stat=stat+rs.getInt(1);  
			}
			  rs.close();
			  ps.close();
			  conn.close();

		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return stat;
	 }
	 public static void fin() {
		 if (values.getL1() && values.getShip()) {
		 try {

			 String query = "UPDATE game_status SET Importer=1 WHERE Game_ID=?";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.execute();
			  ps.close();
			  conn.close();
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }

	 }
	 }
	 
	 public static double[] getFinR(int lev, int sem) {
		 double[] fin = new double[12];
		 try {
			  String query = "Select Red_Ship, White_Ship FROM orders_inv_back WHERE ((Game_ID=?) AND (ava_sem=?) AND (Level=?))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2,sem-1);
			  ps.setInt(3,lev-1);
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
				  fin[0] = rs.getDouble(1) +  rs.getDouble(2);
			}
			  if (sem==0) {
				  fin[0]=8;
			  }
			  rs.close();
			  ps.close();
					   query = "Select SUM(Red_Ship), SUM(White_Ship) FROM orders_inv_back WHERE ((Game_ID=?) AND (ava_sem<=?)  AND (Level=?))";
					   ps = conn.prepareStatement(query); 
					  ps.setInt(1,values.getIDGame());
					  ps.setInt(2,sem-1);
					  ps.setInt(3,lev-1);
					   rs = ps.executeQuery();
					  while ( rs.next() ) {
						  fin[1] = rs.getDouble(1) +  rs.getDouble(2)+32;
					}
					  rs.close();
					  ps.close();
			  query = "SELECT Red_Inv,  White_Inv,  Red_Back,  White_Back FROM orders_inv_back WHERE ((Game_ID=?) AND (Semana=?) AND (Level=?))";
			  ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2,sem-1);
			  ps.setInt(3,lev);
			  rs = ps.executeQuery();
			  while ( rs.next() ) {
				  fin[2] = rs.getDouble(1) +  rs.getDouble(2);
				  fin[8] = rs.getDouble(2);
				  fin[9] = rs.getDouble(1);
				  fin[4] =rs.getDouble(3) +  rs.getDouble(4);
				  fin[10] = rs.getDouble(4);
				  fin[11] = rs.getDouble(3);
			}
			  rs.close();
			  ps.close();
			  query = "SELECT SUM(Red_Inv),  SUM(White_Inv),  SUM(Red_Back),  SUM(White_Back) FROM orders_inv_back WHERE ((Game_ID=?) AND (Semana<=?) AND (Level=?))";
			  ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2,sem-1);
			  ps.setInt(3,lev);
			  rs = ps.executeQuery();
			  while ( rs.next() ) {
				  fin[3] = rs.getDouble(1) +  rs.getDouble(2);
				  fin[5] =rs.getDouble(3) +  rs.getDouble(4);
			}
			  rs.close();
			  ps.close();
			  //sipping
			  query = "SELECT units FROM shipping WHERE ((Game_ID=?) AND (Semana=?) AND (Level=?))";
			  ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2,sem-1);
			  ps.setInt(3,lev);
			  rs = ps.executeQuery();
			  while ( rs.next() ) {
				  fin[6] =rs.getDouble(1);
			}
			  rs.close();
			  ps.close();
			  query = "SELECT SUM(units) FROM shipping WHERE ((Game_ID=?) AND (Semana<=?) AND (Level=?))";
			  ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2,sem-1);
			  ps.setInt(3,lev);
			  rs = ps.executeQuery();
			  while ( rs.next() ) {
				  fin[7] = rs.getDouble(1);
			}
			  rs.close();
			  ps.close();
			  conn.close();
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return fin;
	 }
	 public static double[] getIngR(int lev, int sem) {
		 double[] fin = new double[2];
		 try {
			  String query = "Select Red_Ship, White_Ship FROM orders_inv_back WHERE ((Game_ID=?) AND (ava_sem=?) AND (Level=?))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2,sem-1);
			  ps.setInt(3,lev);
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
				  fin[0] = (rs.getDouble(1) +  rs.getDouble(2));
			  }
			  
			  rs.close();
			  ps.close();
			  query = "Select SUM(Red_Ship), SUM(White_Ship) FROM orders_inv_back WHERE ((Game_ID=?) AND (ava_sem<=?) AND (Level=?))";
			   conn =  connect_DB();
			   ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2,sem-1);
			  ps.setInt(3,lev);
			   rs = ps.executeQuery();
			  while ( rs.next() ) {
				  fin[1] = (rs.getDouble(1) +  rs.getDouble(2) + 16);
			  }
			  rs.close();
			  ps.close();
			  conn.close();	
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return fin;
	 }
	 
	 public static double[] getFinRW(int lev, int sem) {
		 double[] fin = new double[4];
		 try {
			  String query = "Select Red_Ship, White_Ship FROM orders_inv_back WHERE ((Game_ID=?) AND (ava_sem=?) AND (Level=?))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2,sem-1);
			  ps.setInt(3,lev-1);
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
				  fin[0] = rs.getDouble(1) +  rs.getDouble(2);
			}
			  if ((sem-1)<=0) {
				  fin[0]=8;
			  }
			  rs.close();
			  ps.close();
			
			  query = "SELECT Red_Inv,  White_Inv,  Red_Back,  White_Back FROM orders_inv_back WHERE ((Game_ID=?) AND (Semana=?) AND (Level=?))";
			  ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2,sem-1);
			  ps.setInt(3,lev);
			  rs = ps.executeQuery();
			  while ( rs.next() ) {
				  fin[1] = rs.getDouble(1) +  rs.getDouble(2);
				  fin[2] =rs.getDouble(3) +  rs.getDouble(4);
			}
			  rs.close();
			  ps.close();
			 
			  //sipping
			  query = "SELECT units FROM shipping WHERE ((Game_ID=?) AND (Semana=?) AND (Level=?))";
			  ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2,sem-1);
			  ps.setInt(3,lev);
			  rs = ps.executeQuery();
			  while ( rs.next() ) {
				  fin[3] =rs.getDouble(1);
			}
			  rs.close();
			  ps.close();
			  conn.close();
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return fin;
	 }
	 public static double[] getIngRW(int lev, int sem) {
		 double[] fin = new double[1];
		 try {
			  String query = "Select Red_Ship, White_Ship FROM orders_inv_back WHERE ((Game_ID=?) AND (ava_sem=?))";
			  Connection conn =  connect_DB();
			  PreparedStatement ps = conn.prepareStatement(query); 
			  ps.setInt(1,values.getIDGame());
			  ps.setInt(2,sem-1);
			  ResultSet rs = ps.executeQuery();
			  while ( rs.next() ) {
				  fin[0] = rs.getDouble(1) +  rs.getDouble(2);
			  }
			  rs.close();
			  ps.close();
			 
			  conn.close();	
		 } catch (SQLException e) {
				e.printStackTrace();
		          JOptionPane.showMessageDialog( null,
		       "Error in DB connection!" + e,
		       "Error", JOptionPane.ERROR_MESSAGE);
		      }
		 return fin;
	 }
	 
	 public static void pop_tablaFin(JTable Tabla, int lev) {
		        DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		        dm.getDataVector().removeAllElements();
		        double accum = 0;
		        Object[] result = new Object[9];
		        NumberFormat currencyFormatter =  NumberFormat.getCurrencyInstance(Locale.US);	
		        for (int w=-4; w<values.getWeek();w++){
		         if (w<-1) {
		        	 result[0] = w;
		        	 result[1] = currencyFormatter.format(400);
		        	 result[2] = currencyFormatter.format(96);
		        	 result[3] = currencyFormatter.format(0);
		        	 result[4] = currencyFormatter.format(40);
		        	 result[5] = currencyFormatter.format(536);
		        	 result[6] = currencyFormatter.format(640);
		        	 result[7] = currencyFormatter.format(104);
		        	 accum = accum + 104;
		        	 result[8] = currencyFormatter.format(accum);
		        	 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		         } else {
		        	 result[0] = w;
		         	double[] fin = new double[4];
		        	double[] ing = new double[1];
		 			fin = connect.getFinRW(lev,w);
					ing = connect.getIngRW(lev,w);
		        	 result[0] = w;
		        	 result[1] = currencyFormatter.format(fin[0]*50);
		        	 result[2] = currencyFormatter.format(fin[1]*4);
		        	 result[3] = currencyFormatter.format(fin[2]*6);
		        	 result[4] = currencyFormatter.format(fin[3]*10);
		        	 result[5] = currencyFormatter.format(fin[0]*10+fin[1]*4+fin[2]*6+fin[3]*30);
		        	 result[6] = currencyFormatter.format(ing[0]*80);
		        	 result[7] = currencyFormatter.format(ing[0]*80-(fin[0]*50+fin[1]*4+fin[2]*6+fin[3]*10));
		        	 accum = accum + ing[0]*80-(fin[0]*50+fin[1]*4+fin[2]*6+fin[3]*10);
		        	 result[8] = currencyFormatter.format(accum);
		        	 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		         }
		        	
		        	
		        }
		        Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		        
		  
	 }
	 
	 public static void pop_tablaInv(JTable Tabla, int lev) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[3];
		    try {
		  		  String query = "SELECT Semana, Red_Inv,White_Inv FROM orders_inv_back WHERE ((Game_ID=?) AND (Level=?)) Order by Semana DESC";
		  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, lev);
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  for (int i=0;i<3;i++){
		  				result[i] = rs.getInt(i+1);
		  			  }
	
		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
				    rs.close();
				    ps.close();
				    conn.close();
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 } 
	 public static void pop_tablaBack(JTable Tabla, int lev) throws SQLException {
		    DefaultTableModel dm = (DefaultTableModel)Tabla.getModel();
		    dm.getDataVector().removeAllElements();
		        Object[] result = new Object[3];
		    try {
		  		  String query = "SELECT Semana, Red_back,White_Back FROM orders_inv_back WHERE ((Game_ID=?) AND (Level=?)) Order by Semana DESC";
		  		
		  		  Connection conn =  connect_DB();
		  		  PreparedStatement ps = conn.prepareStatement(query);
		  		  ps.setInt(1, values.getIDGame());
		  		  ps.setInt(2, lev);
		  		  ResultSet rs = ps.executeQuery();
		  		  while ( rs.next() ) {
		  			  for (int i=0;i<3;i++){
		  				result[i] = rs.getInt(i+1);
		  			  }
	
		  			 ((DefaultTableModel)Tabla.getModel()).addRow(result);
		  		  }
				    rs.close();
				    ps.close();
				    conn.close();
				    Tabla.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); 
		  		}
				  catch (SQLException se)
				  {
				    // log exception;
				    throw se;
				  }
	 } 
}
